<?php
	require("frmCookie.php");

	$iOpcion = $_GET[iOpcion];
?>
<html>
	<head>
    	<?php
		require("frmHeader.php");
		?>
	</head>
	<body>
    	<table cellpadding="0" cellspacing="0" align="center" class="tablaPrincipal">
        	<tr>
            	<td align="center">
                	<?php
					require("frmSession.php"); 
					
					//Redirecciona a SINPERMISOS.PHP en caso de no tener permisos
					
					if (!(in_array(37000, $_SESSION[permisos])))
					{
					?>
					  <script language="JavaScript" type="text/javascript">
					  <!--
					  window.location.href = "sinPermisos.php"
					  //-->
					  </script>
					<?php
					}
					$titulo = "Reporte Abastecimiento de Stock";
					?>
                </td>
            </tr>
            <tr>
            	<td align="center">
                	<?php
					require("frmTitulo.php");
					?>
                </td>
            </tr>
            <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
            	<td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="menuPrincipal">
                    	<tr>
                        	<td>
								<?php
                                require("frmMenu.php");
                                ?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
            <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="tituloPantalla">
                    	<tr>
                        	<td align="center">
                            	<p class="texto" align="left"><?php echo $titulo; ?></p>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
             <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="estadoPantalla">
                    	<tr>
                        	<td align="center">
                            	<p class="texto" align="left"><?php echo $mensaje; ?></p>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
             <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="contenedorPrincipal">
                    	<tr>
                        	<td align="">
                        		<?php
                        		switch($iOpcion)
                        		{
                        			case 1:
                        			{
                				?>
                				<form name="frm" action="fijacionStockReporte.php?iOpcion=2" method="post">
                					<table>
                						<tr>
                							<td>Sucursal: </td>
                							<td>
                								<select name="cboSucursal">
            									<?php
		    							        $mySQL = "SELECT * FROM sucursales";	
		    							        $result = readSQL($mySQL);
				  				                foreach($result as $value)
				  				                {
			  					                	echo "<option value='" . $value[0] . "'>" . $value['nombre'] . "</option>";
				  				                }
		    							        ?>
                								</select>
                							</td>
                						</tr>
                						<tr>
                							<td colspan="2" align="center"><input type="submit" name="cmdEnviar" value="Generar" /></td>
                						</tr>
                					</table>
                				</form>
                				<?php
                        				break;
                        			}
                        			case 2:
                        			{	
                        				$mySQL = "SELECT nombre FROM sucursales WHERE idSucursal=" . $_POST['cboSucursal'];
                        				$result = readSQL($mySQL);
		  				                foreach($result as $value)
		  				                {
	  					                	$nombreSucursal = $value['nombre'];
		  				                }
                        				
                        				$data = array();
		                        			
		                        		$head = array();
		                        		
		                        		array_push($head, "Codigo Tango");
		                        		array_push($head, "Codigo Fabricante");
		                        		array_push($head, "Descripcion Interna");
		                        		array_push($head, "Descripcion Fabricante");
		                        		array_push($head, "Tipo de Parte");
		                        		array_push($head, "Modelos");
		                        		array_push($head, "Cantidad Fijada");
		                        		array_push($head, "Cantidad Existente");
		                        		array_push($head, "Cantidad a Enviar");
										array_push($head, "Cantidad disponible en ".$_SESSION['sucursal']);
		                        		
		                        		array_push($data, $head);
		                        		
		                        		$mySQL = "SELECT DISTINCT(P.codigoTango), ";
										$mySQL = $mySQL . "P.codigoTango, ";
										$mySQL = $mySQL . "P.codigoFabricante, ";
										$mySQL = $mySQL . "P.idParte, ";
										$mySQL = $mySQL . "P.descripcionInterna, ";
										$mySQL = $mySQL . "P.descripcionFabricante, ";
										$mySQL = $mySQL . "TP.nombre, ";
										$mySQL = $mySQL . "FS.cantidad AS fijacionstock, ";
										$mySQL = $mySQL . "COALESCE((SELECT SUM(S.cantidad) FROM stock S WHERE S.idParte = P.idParte AND S.idAlmacen IN(SELECT idAlmacen FROM almacenes WHERE idSucursal=" . $_POST['cboSucursal'] . " and nombre not like '%Danad%s%' and nombre not like '%Irreparables%')), 0) AS stock, ";
										$mySQL = $mySQL . "COALESCE((SELECT SUM(S.cantidad) FROM stock S WHERE S.idParte = P.idParte AND S.idAlmacen IN(SELECT idAlmacen FROM almacenes WHERE idSucursal=" . $_SESSION['idSucursal'] . " and nombre not like '%Danad%s%' and nombre not like '%Irreparables%')), 0) AS stockSusursalActual ";
										$mySQL = $mySQL . "FROM ";
										$mySQL = $mySQL . "partes P, ";
										$mySQL = $mySQL . "tipospartes TP, ";
										$mySQL = $mySQL . "fijacionstock FS ";
										$mySQL = $mySQL . "WHERE ";
										$mySQL = $mySQL . "P.idTipoParte = TP.idTipoParte ";
										$mySQL = $mySQL . "AND P.idParte = FS.idParte ";
										$mySQL = $mySQL . "AND FS.idSucursal = " . $_POST['cboSucursal'];
										$mySQL = $mySQL . " GROUP BY P.codigoTango";
										
										//echo $mySQL;
										//exit;
		                        		
										$result = readSQL("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;".$mySQL);
													
										foreach($result as $value)
										{
											$registro = array();
											
											$mySQL = "SELECT M.nombreComercial ";
											$mySQL = $mySQL . "FROM modelos M, partesxmodelo PXM ";
											$mySQL = $mySQL . "WHERE M.idModelo = PXM.idModelo AND PXM.idParte = " . $value['idParte'];
											
											$modelos = "";
											$cantModelos = 0;
											$resultModelos = readSQL("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;".$mySQL);
											foreach($resultModelos as $valueModelos)
											{
												if($cantModelos == 0)
													$modelos = $valueModelos['nombreComercial'];
												else
													$modelos = $modelos . " - " . $valueModelos['nombreComercial'];
													
												$cantModelos++;
											}

											array_push($registro, $value['codigoTango']);
											array_push($registro, $value['codigoFabricante']);
											array_push($registro, $value['descripcionInterna']);
											array_push($registro, $value['descripcionFabricante']);
											array_push($registro, $value['nombre']);
											array_push($registro, $modelos);
											array_push($registro, $value['fijacionstock']);
											array_push($registro, $value['stock']);
											array_push($registro, $value['fijacionstock'] - $value['stock']);
											array_push($registro, $value['stockSusursalActual']);
											
											array_push($data, $registro);
										}
		                        		
		                        		// include package
										require_once("Spreadsheet/Excel/Writer.php");
										
										// Fecha Hora Inicio
										$fecha = getdate();
										$dia = $fecha[mday];
										$mes = $fecha[mon];
										$ano = $fecha[year];
										$hora = $fecha[hours];
										$minutos = $fecha[minutes];
										$segundos = $fecha[deconds];
										$archivo =  $ano . "-" . $mes . "-" . $dia . "-" . $hora . "-" . $minutos . "-" . $segundos . "FijacionStock.xls";
										
										// create empty file
										//$excel = new Spreadsheet_Excel_Writer("/var/www/html/galander/informes/" . $archivo);
										if($_SERVER['SERVER_NAME'] == "localhost")
										{
											$host = "/Users/fede/Sites/galander/informes/";
										}
										else
										{
											$host = "/var/www/html/galander/informes/";	
										}
										$excel = new Spreadsheet_Excel_Writer($host . $archivo);
										
										// add worksheet
										$sheet =& $excel->addWorksheet("Hoja 1");
										
										// create format for header row 
										// bold, red with black lower border
										/*$firstRow =& $excel->addFormat();
										$firstRow->setBold();
										$firstRow->setColor('black');
										$firstRow->setBottom(1);*/
										//$firstRow->setBottomColor('black');
										
										// create format for header column
										// blue with black right border
										/*$firstCol =& $excel->addFormat();
										$firstRow->setBold();
										$firstCol->setColor('black');*/
										//$firstCol->setRight(1);
										//$firstCol->setRightColor('black');
										
										// create format for top left corner
										/*$firstRowCol =& $excel->addFormat();
										$firstRowCol->setBottom(1);*/
										//$firstRowCol->setRight(1);
										//$firstRowCol->setBorderColor('black');
										
										// create format for numeric cells
										
										$codigoTango =& $excel->addFormat();
										$codigoTango->setNumFormat('000000000000');
										
										/*$num =& $excel->addFormat();
										$num->setNumFormat('#');
										
										
										$prodcode =& $excel->addFormat();
										$prodcode->setNumFormat('0000000');
										
										$fecIng =& $excel->addFormat();
										$fecIng->setNumFormat('mm-yyyy-dd');
										
										$modelo =& $excel->addFormat();
										$modelo->setAlign('left');*/
										
										// create format for averaged values
										/*$avg =& $excel->addFormat();
										$avg->setNumFormat('0.00');*/
										
										// add data to worksheet
										$rowCount = 0;
										foreach ($data as $row) 
										{
											for($colCount = 0; $colCount < sizeof($row); $colCount++) 
											{
											    switch($colCount)
											    {
											    	case 0:
											    	{
											    		$format = 'codigoTango';
											    		break;
											    	}
											    	default:
											    	{
											    		$format = "";
											    		break;
											    	}
											    }
											    /*if ($rowCount == 0 && $colCount == 0) {
											      $format = 'firstRowCol';
											    } else if ($rowCount == 0) {
											        $format = 'firstRow';        
											    } else if ($colCount == 0) {
											        $format = 'firstCol';                
											    } else {
											        $format = 'num';                      
											    }*/
											    /*if($colCount == 11)
											    	$format = 'imeiF';
											    else if($colCount == 15)
											    	$format = 'prodcode';
											    else if($colCount == 19)
											    	$format = 'modelo';
											    else
											    	$format = 'num';*/
											    /*if($colCount == 4 || $colCount == 8 || $colCount == 9)
											    {
											    	$format = 'num';
											    }*/
										    	
										    	if($format != "")
										    		$sheet->write($rowCount, $colCount, $row[$colCount], $$format);
										    	else
										    		$sheet->write($rowCount, $colCount, $row[$colCount]);
										    	
										   		//$sheet->write($rowCount, $colCount, $row[$colCount], $$format);                       
										   		//$sheet->write($rowCount, $colCount, $row[$colCount]);
										  	}  
										  // get cell coordinates
										  $start = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, 1);
										  $end = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, (sizeof($row)-1));
										  // add average() formula to terminating cell of each row
										  // except the first (header) row
										  /*if ($rowCount != 0) {
										    $sheet->writeFormula($rowCount, sizeof($row), "=AVERAGE($start:$end)", $avg);     
										  } */
										  $rowCount++;
										}
										
										// save file to disk
										if ($excel->close() === true) {
										  //echo 'Spreadsheet successfully saved!';
									      echo "<a class='linknuevo' href='informes/" . $archivo . "'>Descargar Archivo</a>";  
										} else {
										  echo 'ERROR: Could not save spreadsheet.';
										}		
		                        		
                        				break;
                        			}
                        		}
                        		?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
	</body>
</html>